﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace ManagementProject
{
    public partial class frmCategory : Form
    {
        SqlConnection conn;
        SqlDataAdapter da;
        DataSet ds;
        DataTable dt;
        DataView dv;
        SqlCommandBuilder cb;
        string error = null;
        string ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        public frmCategory()
        {
            InitializeComponent();
            loadCategoriesFormDB();
            loadDataIntodgdvCategory();
            loadItemsIntoCboSearch();
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            timerClearStatus.Start();
        }

        void creatConnectionToDB()
        {
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
                error += "\n";
                error += ex.Message;
                lblStatus.Text = "Wrong connectionString";
            }
            
        }
        void loadCategoriesFormDB()
        {
            creatConnectionToDB();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Production.getCategories";
                da = new SqlDataAdapter(cmd);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds, "Categories");
            }
            catch (System.Exception ex)
            {
                error += "\n";
                error += ex.Message;
                MessageBox.Show(error);
                lblStatus.Text = "Load database failed!";
            }
            finally
            {
                conn.Close();
            }

            //comand
            #region loadbySelected
            //SqlConnection conn = new SqlConnection();
            //conn.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            //conn.Open();
            //SqlCommand cmd = new SqlCommand();
            //cmd.Connection = conn;            
            //cmd.CommandType = CommandType.Text;
            //cmd.CommandText = "SELECT c.categoryid, c.categoryname, c.[description] "
            //                   +"FROM Production.Categories c";
            //SqlDataReader dr = cmd.ExecuteReader();
            //while(dr.Read()){
            //    dgdvCategory.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
            //}
            //dr.Close();
            //conn.Close();
            //#endregion
            //#region addItemsInCboSearchCategory
            //List<string> listItemsSearch = new List<string>();
            //listItemsSearch.Add("ID");
            //listItemsSearch.Add("Name");
            //listItemsSearch.Add("Description");
            //cboSearchBy.DataSource = listItemsSearch;
            #endregion
        }
        void loadDataIntodgdvCategory()
        {
            try
            {
                dt = ds.Tables["Categories"];
                dv = new DataView(dt);
                dgdvCategory.DataSource = dt;
                dgdvCategory.ClearSelection();
            }
            catch (System.Exception ex)
            {
                disableAll();
                error += "\n";
                error += ex.Message;
            }

        }
        void loadItemsIntoCboSearch()
        {
            List<string> list = new List<string>();
            list.Add("ID");
            list.Add("Name");
            list.Add("Description");
            cboSearchBy.DataSource = list;
        }

        void clear()
        {
            txtCategoryID.Text = null;
            txtCategoryName.Text = null;
            txtDescription.Text = null;
            txtSearch.Text = null;
            dgdvCategory.ClearSelection();
        }
        void disableAll()
        {
            btnAdd.Enabled = false;
            btnUpdate.Enabled = false;
            btnNew.Enabled = false;
            btnDelete.Enabled = false;
            txtCategoryName.Enabled = false;
            txtDescription.Enabled = false;
            cboSearchBy.Enabled = false;
            txtSearch.Enabled = false;
            btnSearch.Enabled = false;
        }
        bool validateInput()
        {
            bool error = false;
            string categoryName = txtCategoryName.Text.Trim();
            string categoryDescription = txtDescription.Text.Trim();
            if (categoryName.Length == 0)
            {
                errorInput.SetError(txtCategoryName, "Please enter Category Name");
                error = true;
            }
            if (categoryName.Length > 15)
            {
                errorInput.SetError(txtCategoryName, "Category Name too long");
                error = true;
            }
            if (categoryDescription.Length == 0)
            {
                errorInput.SetError(txtDescription, "Please enter Description");
                error = true;
            }
            if (categoryDescription.Length > 200)
            {
                errorInput.SetError(txtDescription, "Description too long");
                error = true;
            }
            if (error == true)
            {                
                return false;
            }
            else
            {                
                return true;
            }
        }

        void addCategory()
        {
            if (validateInput())
            {
                try
                {
                    creatConnectionToDB();
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Production.insertCategories";

                    SqlParameter param = new SqlParameter("@categoryname", SqlDbType.NVarChar);
                    param.Size = 15;
                    param.Value = txtCategoryName.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@description", SqlDbType.NVarChar);
                    param.Size = 200;
                    param.Value = txtDescription.Text;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    loadCategoriesFormDB();
                    loadDataIntodgdvCategory();
                    clear();
                    lblStatus.Text = "Add successful!";

                }
                catch (System.Exception ex)
                {
                    error += "\n";
                    error += ex.Message;
                    MessageBox.Show(ex.Message);
                    lblStatus.Text = "Add failed!";                    
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        void updateCategory()
        {          
            
                if (validateInput())
                {
                    DialogResult confirm = MessageBox.Show("Are you sure?", "Update Category", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (confirm == DialogResult.Yes)
                    {
                        creatConnectionToDB();
                        try
                        {
                            conn.Open();
                            SqlCommand cmd = new SqlCommand();
                            cmd.Connection = conn;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = "Production.updateCategories";

                            SqlParameter param = new SqlParameter("@categoryid", SqlDbType.Int);
                            param.Value = int.Parse(txtCategoryID.Text);
                            cmd.Parameters.Add(param);

                            param = new SqlParameter("@categoryname", SqlDbType.NVarChar);
                            param.Size = 15;
                            param.Value = txtCategoryName.Text;
                            cmd.Parameters.Add(param);

                            param = new SqlParameter("@description", SqlDbType.NVarChar);
                            param.Size = 200;
                            param.Value = txtDescription.Text;
                            cmd.Parameters.Add(param);

                            cmd.ExecuteNonQuery();                            
                            lblStatus.Text = "Update successful!";
                            loadCategoriesFormDB();
                            loadDataIntodgdvCategory();
                        }
                        catch (System.Exception ex)
                        {
                            lblStatus.Text = "Update failed!";
                            error += ex.Message;
                        }
                        finally
                        {
                            conn.Close();
                        }
                }
            }
        }
        void deleteCategory() 
        {          
            try
            {
                DialogResult result = MessageBox.Show("Are you sure?", "Delete category", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.Yes)
                {
                    creatConnectionToDB();
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Production.delCategories";
                    SqlParameter param = new SqlParameter("@categoryid", SqlDbType.Int);
                    param.Value = int.Parse(txtCategoryID.Text);
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    loadCategoriesFormDB();
                    loadDataIntodgdvCategory();
                    lblStatus.Text = "Delete successful!";
                }
            }
            catch (System.Exception ex)
            {
                lblStatus.Text = "Delete fail!";
                error += "\n";
                error += ex.Message;
                MessageBox.Show(string.Format("May it's in using by another then you can't remove it \n Support: chanhvqse61295@fpt.edu.vn"),
                                "Information",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }                
                    
        }
        void searchCategory() 
        {
            string searchBy = cboSearchBy.Text.Trim();
            if (txtSearch.Text.Length > 0)
            {
                try
                {
                    if (searchBy.Equals("ID"))
                    {
                        try
                        {
                            dv = new DataView(dt);
                            dv.RowFilter = string.Format("[Category ID] = {0}", int.Parse(txtSearch.Text.Trim()));
                            dgdvCategory.DataSource = dv;
                            dgdvCategory.ClearSelection();
                            lblStatus.Text = "Search successful!";
                        }
                        catch (System.Exception ex)
                        {
                            errorInput.SetError(txtSearch,"ID must be number!");
                        }
                        
                    }
                    if (searchBy.Equals("Name"))
                    {
                        dv = new DataView(dt);
                        dv.RowFilter = string.Format("[Category Name] LIKE '%{0}%'", txtSearch.Text.Trim().ToString());
                        dgdvCategory.DataSource = dv;
                        dgdvCategory.ClearSelection();
                        lblStatus.Text = "Search successful!";
                    }
                    if (searchBy.Equals("Description"))
                    {
                        dv = new DataView(dt);
                        dv.RowFilter = string.Format("[Description] LIKE '%{0}%'", txtSearch.Text.Trim().ToString());
                        dgdvCategory.DataSource = dv;
                        dgdvCategory.ClearSelection();
                        lblStatus.Text = "Search successful!";
                    }
                }
                catch (System.Exception ex)
                {
                    error += ex.Message;
                    lblStatus.Text = "Search failed!";
                }
                finally
                {
                    clear();
                }
            } 
            else
            {
               errorInput.SetError(txtSearch,"Pleas enter value");
            }

            btnNew.Enabled = true;
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
                
        }       

        private void btnNew_Click(object sender, EventArgs e)
        {
            clear();
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            lblStatus.Text = null;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            addCategory();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            updateCategory();            
            clear();
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            dgdvCategory.ClearSelection();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            deleteCategory();
            clear();
            btnAdd.Enabled = true;
            btnNew.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            dgdvCategory.ClearSelection();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            searchCategory();
            //command
            #region searchBySQLQueryConnection
            //SqlConnection conn = new SqlConnection();
            //conn.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            //conn.Open();
            //SqlCommand cmd = conn.CreateCommand();         
            //cmd.CommandText = "SELECT c.categoryid, c.categoryname, c.[description] "
            //                   + "FROM Production.Categories c ";
            //if (cboSearchBy.Text.Equals("ID"))
            //{
            //    cmd.CommandText += "WHERE c.categoryid LIKE @par";
            //    cmd.Parameters.Add(new SqlParameter("par", string.Format("{0}", txtSearch.Text)));
            //}
            //if (cboSearchBy.Text.Equals("Name"))
            //{
            //    cmd.CommandText += "WHERE c.categoryname LIKE @par";
            //    cmd.Parameters.Add(new SqlParameter("par", string.Format("%{0}%", txtSearch.Text)));
            //}
            //if (cboSearchBy.Text.Equals("Description"))
            //{
            //    cmd.CommandText += "WHERE c.[description] LIKE @par";
            //    cmd.Parameters.Add(new SqlParameter("par", string.Format("%{0}%", txtSearch.Text)));
            //}
            //SqlDataReader dr = cmd.ExecuteReader();
            //while (dr.Read())
            //{
            //    dgdvCategory.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
            //}
            //dr.Close();
            //conn.Close();
            #endregion
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            loadCategoriesFormDB();
            loadDataIntodgdvCategory();
            lblStatus.Text = "Reload successful!";
        }

        private void dgdvCategory_SelectionChanged(object sender, EventArgs e)
        {
            if (dgdvCategory.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                DataGridViewRow r = dgdvCategory.SelectedRows[0];
                txtCategoryID.Text = r.Cells[0].Value.ToString();
                txtCategoryName.Text = r.Cells[1].Value.ToString();                
                txtDescription.Text = r.Cells[2].Value.ToString();
            }
        }

        private void timerClearStatus_Tick(object sender, EventArgs e)
        {
            lblStatus.Text = null;
            errorInput.Clear();
        }

       
    }
}
